Skip to main content Skip to complementary content

Examples of concat functions

The CONCAT() function should not be confused with the CONCATENATE script keyword.

In its simplest form, Concat() is used to string / join together values/words/selections into one string. However, it can be utilized in a number of ways to help you solve different problems. All the examples are based upon the following table:

Example string table data
Example string table data
Table MyColumn Date Value
Data JKL 01/01/2012 11
Data1 WVX 01/02/2012 13
Data GHI 01/03/2012 13
Data ABC 01/04/2012 15
Data1 STU 01/05/2012 18
Data1 PQR 01/06/2012 10
Data1 MNO 01/07/2012 25
Data DEF 01/08/2012 11

Simple String Concatenation

As mentioned earlier, the concat function lets you string together a list of values. These values can be hard coded or driven by selections/data.

=CONCAT(MyColumn,',')

=CONCAT(DISTINCT MyColumn,',')

This simple concat statement would string together all of the possible values from the column MyColumn. You may wish to add the DISTINCT keyword. This would ensure that each value is only displayed once in the string:

ABC,DEF,GHI,JKL,MNO,PQR,STU,VWX

When using a simple concat, you have the option to add a sort weight to the function to order the string values by a column of your choice. In the example below, the date column is added to sort the values.

=CONCAT(MyColumn, ',', Date)

Result: JKL,VWX,GHI,ABC,STU,PQR,MNO,DEF

Concat() within an Expression/Set Statement

Example: Passing multiple dynamic values to a set analysis expression

There are occasions when you want to pass a dynamic selection of values to a set statement. To do this you need to add some single quotes to the string so that the CONCAT() function returns e.g. 'JKL','VWX'. But you cannot have the single quotes as they are, since they then would be interpreted when the Concat is evaluated instead of when the set expression is evaluated. Instead, use the Chr() function:

=CONCAT(Chr(39)&MyColumn&Chr(39),',')

You can then pass this concat statement to the inside of an expression:

=Sum({<MyColumn={$(=CONCAT(Chr(39)&MyColumn&Chr(39),','))}>} Value)

In most instances, this technique would be used where data islands are present. It lets you pass values to an expression that will not affect any part of the data model as the data island table is not joined to it.

Concat() in the Script

Concat can also be used in the script to convert multiple rows into one single column value, just as any other aggregation.

Remembering the source data used earlier, the result of script side CONCAT is as follows:

ConcatExample:
Load Table,
Concat(MyColumn,',') as CombinedData
Resident Temp
Group By Table;

The following is the result of this script function:

Example output in table form
Table CombinedData
Data ABC,DEF,GHI,JKL
Data1 MNO,PQR,STU,WVX

Using Rank() to influence the Concat()

When you start to utilize other function in conjunction with concat() you can start to achieve clever results. In this example, Rank() has been used to grab the top three performers (based on Value) and string them together.

=CONCAT(IF(aggr(Rank(sum(Value)),MyColumn)<=3,MyColumn),',')

Result: ABC,MNO,STU

Did this page help you?

If you find any issues with this page or its content – a typo, a missing step, or a technical error – let us know how we can improve!

Join the Analytics Modernization Program

Remove banner from view

Modernize without compromising your valuable QlikView apps with the Analytics Modernization Program. Click here for more information or reach out: ampquestions@qlik.com